package com.fxwater.websocket.job;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.fxwater.websocket.util.SpringBeanFinder;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.Serializable;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.regex.Pattern;

/**
 * @Description: 第二个业务场景 ，基于泵站和时间生成数据的任务调度作业类
 * @Author: 王明朝
 * @Date: 2019/8/17 19:59
 */
public class ScheduledPumpStationDataSumaryJob implements Job {

    private final long DELAY = 1000;

    private final String[] measureObjects = new String[]{
            "In_Flow",
            "In_Flow_Sum",
            "In_Pressure",
            "In_Turb",
            "In_YL",
            "Out_Flow",
            "Out_Flow_Sum",
            "Out_Pressure",
            "Out_Turb",
            "Out_YL"
    };

    private Logger logger = LoggerFactory.getLogger(ScheduledPumpStationDataSumaryJob.class);




    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        logger.info("--------------------------------开始缓存第二场景数据--------------------------");
        try {
            ThreadPoolExecutor executor =new ThreadPoolExecutor(1, 1,
                    0L, TimeUnit.MILLISECONDS,
                    new LinkedBlockingQueue<Runnable>());
            executor.execute(new Runnable() {
                @Override
                public void run() {
                     RedisTemplate<String, Serializable> redisTemplate = SpringBeanFinder.findBean("stringRedisTemplate");
                     JdbcTemplate jdbcTemplate = SpringBeanFinder.findBean(JdbcTemplate.class);
                    while(redisTemplate==null|| jdbcTemplate==null){
                        try {
                            Thread.sleep(DELAY);
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                        redisTemplate = SpringBeanFinder.findBean(RedisTemplate.class);
                        jdbcTemplate = SpringBeanFinder.findBean(JdbcTemplate.class);
                    }
                    String sql = "select pump_name,pump_code from fx_pump_station";
                    List<Map<String,Object>> pumpStationList = jdbcTemplate.queryForList(sql);
                    final JdbcTemplate myJdbcTemplate = jdbcTemplate;
                    final RedisTemplate<String, Serializable> myRedisTemplate = redisTemplate;
                    if(CollectionUtil.isNotEmpty(pumpStationList)){
                        pumpStationList.forEach(map->{
                            String pompCode = String.valueOf(map.get("pump_code"));
                            if(StrUtil.isNotEmpty(pompCode) && !"null".equals(pompCode)){
                                for(String obj:measureObjects){
                                    logger.info("--------------------------------开始缓存第二场景数据,泵站{}，测量量{}的数据--------------------------",pompCode,obj);
                                    DateTime dateTime =  DateUtil.beginOfDay(new Date());
                                    java.sql.Date correntDate = dateTime.toSqlDate();
                                    String querySql="SELECT " +
                                                "r.item_val,r.item_code,i.item_name," +
                                                "LEFT ( CONVERT ( VARCHAR ( 100 ), r.lastchange, 24 ), 5 ) lastchange," +
                                                "(" +
                                                "CASE " +
                                                "WHEN ( i.is_flag_warning= 1 AND r.item_val> 0 ) THEN" +
                                                " 1 " +
                                                " WHEN ( i.is_updown_warning= 1 AND ( r.item_val< i.fz_down OR r.item_val> i.fz_up ) ) THEN " +
                                                " 2 ELSE 0  " +
                                                " END  " +
                                                " ) is_warning ,i.unit_zh,i.unit_flag" +
                                                " FROM " +
                                                " fx_real_data r " +
                                                " LEFT JOIN fx_item i ON r.item_code= i.item_code " +
                                                " WHERE " +
                                                " i.pump_code=? " +
                                                " AND r.item_code LIKE ?  " +
                                                " AND r.lastchange>=?";
                                    List<Map<String,Object>> list = myJdbcTemplate.queryForList(querySql,pompCode,"%"+obj,correntDate);
                                    if(CollectionUtil.isNotEmpty(list)){
                                        Map<String,Object> data = list.get(0);
                                        String date = dateTime.toString("yyyyMMdd");
                                        String prefix = "SECOND_"+ pompCode+"_"+date;
                                        String redisFlag = prefix+"_"+obj+"_LAST_UPDATE";
                                        String currenTime = String.valueOf(data.get("lastchange"));
                                        if(myRedisTemplate.hasKey(redisFlag)){
                                            String lastTime = (String) myRedisTemplate.opsForValue().get(redisFlag);
                                            if(lastTime.equals(currenTime)){
                                                continue;
                                            }
                                        }
                                        myRedisTemplate.opsForValue().set(redisFlag,currenTime);
                                        myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                        redisFlag = prefix+"_"+obj+"_VAL";
                                        String val = NumberUtils.formatNumber(String.valueOf(data.get("item_val")));
                                        myRedisTemplate.opsForValue().set(redisFlag,val);
                                        myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);


                                        redisFlag = prefix + "_" + obj+"_unit_zn";
                                        String unitZn =data.get("unit_zh")!=null? String.valueOf(data.get("unit_zh")):"";
                                        myRedisTemplate.opsForValue().set(redisFlag, unitZn);
                                        myRedisTemplate.expire(redisFlag, 1, TimeUnit.DAYS);


                                        redisFlag = prefix + "_" + obj+"_unit_flag";
                                        String unitFlag = data.get("unit_flag")!=null?String.valueOf(data.get("unit_flag")):"";
                                        myRedisTemplate.opsForValue().set(redisFlag, unitFlag);
                                        myRedisTemplate.expire(redisFlag, 1, TimeUnit.DAYS);

                                        String val_ = val;
                                        if(StrUtil.isNotEmpty(unitZn)){
                                            val_+=unitZn;
                                        } else if(StrUtil.isNotEmpty(unitFlag)){
                                            val_+=unitFlag;
                                        }


                                        redisFlag = prefix + "_" + obj+"VAL_";
                                        myRedisTemplate.opsForValue().set(redisFlag, val_);
                                        myRedisTemplate.expire(redisFlag, 1, TimeUnit.DAYS);

                                        String is_warning = String.valueOf(data.get("is_warning"));
                                        redisFlag = prefix+"_warning";
                                        if(StrUtil.isNotEmpty(is_warning) && !"null".equals(is_warning)&&!"0".equals(is_warning)){
                                            JSONArray jsonArray = null;
                                            if(myRedisTemplate.hasKey(redisFlag)){
                                                String warningInfo =  (String)myRedisTemplate.opsForValue().get(redisFlag);
                                                if(JSONUtil.isJsonArray(warningInfo)){
                                                    jsonArray = JSONUtil.parseArray(warningInfo);
                                                } else {
                                                    jsonArray = new JSONArray();
                                                }
                                            } else {
                                                jsonArray = new JSONArray();
                                            }
                                            JSONObject jsonObject = new JSONObject();
                                            jsonObject.put("item_name",data.get("item_name"));
                                            jsonObject.put("item_code",data.get("item_code"));
                                            jsonObject.put("item_val",NumberUtils.formatNumber(String.valueOf(data.get("item_val"))));
                                            jsonObject.put("item_type",obj);
                                            jsonObject.put("in_out_type",obj.startsWith("In")?"进水口":"出水口");
                                            jsonObject.put("warn_time",data.get("lastchange"));
                                            jsonObject.put("warn_date",date);
                                            jsonObject.put("warn_type",data.get("is_warning"));
                                            jsonObject.put("warn_type_name","1".equals(String.valueOf(data.get("is_warning")))?"信号告警":"上下限告警");
                                            jsonArray.put(jsonObject);
                                            myRedisTemplate.opsForValue().set(redisFlag,JSONUtil.toJsonStr(jsonArray));
                                            myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMM");
                                            String dateMonth = LocalDateTime.now().format(dateTimeFormatter);
                                            String tableName = "fx_warning_data_"+dateMonth;
                                            String checkSql ="select count(1) as ct from  sysobjects where name=? and  type='U'";
                                            List<Map<String,Object>> datas = myJdbcTemplate.queryForList(checkSql,tableName);
                                            int tableNum = 0;
                                            if(CollectionUtil.isNotEmpty(datas)){
                                                Map<String,Object> res = datas.get(0);
                                                String value = String.valueOf(res.get("ct"));
                                                tableNum = Integer.parseInt(value);
                                            }
                                            if(tableNum<1){
                                                    String createTableSql  = "CREATE TABLE [dbo].["+tableName+"](" +
                                                            "  [id] bigint  IDENTITY(1,1) NOT NULL," +
                                                            "  [is_valid] varchar(1) COLLATE Chinese_PRC_CI_AS DEFAULT ((1)) NOT NULL," +
                                                            "  [is_deleted] varchar(1) COLLATE Chinese_PRC_CI_AS DEFAULT ((0)) NOT NULL," +
                                                            "  [create_by] varchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [create_time] date DEFAULT (getdate()) NOT NULL," +
                                                            "  [update_by] varchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [update_time] date DEFAULT (getdate()) NOT NULL," +
                                                            "  [remark] nvarchar(512) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [pump_code] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [item_code] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [item_name] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [item_val] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [item_type] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [warn_time] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [warn_date] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [warn_type] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [warn_type_name] nvarchar(32) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  [in_out_type] nvarchar(10) COLLATE Chinese_PRC_CI_AS  NULL," +
                                                            "  CONSTRAINT "+tableName+"_pk PRIMARY KEY CLUSTERED(id ASC) ON [PRIMARY] " +
                                                            " ) ";
                                                    myJdbcTemplate.execute(createTableSql);
                                                    String[] comments = new String[]{
                                                            "ID","id",
                                                            "is_valid","0：无效，1：有效",
                                                            "is_deleted","1：已删除，0：未删除",
                                                            "create_by","创建人",
                                                            "create_time","创建时间",
                                                            "update_by","更新人",
                                                            "update_time","更新时间",
                                                            "remark","备注",
                                                            "pump_code","泵站编码",
                                                            "item_code","监测项编码",
                                                            "item_name","监测项名称",
                                                            "item_val","监测项值",
                                                            "item_type","监测项类型",
                                                            "warn_time","告警时间",
                                                            "warn_date","告警日期",
                                                            "warn_type","告警类型",
                                                            "warn_type_name","告警类型名称",
                                                            "in_out_type","进水出水类型",
                                                    };
                                                    for(int i=0;i<comments.length;i+=2){
                                                        String commentsSql = "EXECUTE sp_addextendedproperty 'MS_Description', N'"+comments[i+1]+"', 'SCHEMA', N'dbo', 'TABLE', N'"+tableName+"','COLUMN', N'"+comments[i]+"'";
                                                        myJdbcTemplate.execute(commentsSql);
                                                    }
                                                String commentsSql = "EXECUTE sp_addextendedproperty 'MS_Description', N'告警数据表（"+dateMonth+"）', 'SCHEMA', N'dbo', 'TABLE', N'"+tableName+"'";
                                                myJdbcTemplate.execute(commentsSql);
                                            }

                                            String insertSql = "insert into  "+tableName+" (pump_code,item_code,item_name,item_val,item_type,warn_time,warn_date,warn_type,warn_type_name,in_out_type) values( ?,?,?,?,?,?,?,?,?,?)";
                                            myJdbcTemplate.update(
                                                    insertSql,pompCode,data.get("item_code"),data.get("item_name"),data.get("item_val")==null?"":NumberUtils.formatNumber(String.valueOf(data.get("item_val"))),obj,data.get("lastchange"),
                                                    dateTime.toString("yyyy-MM-dd"),data.get("is_warning"),"1".equals(String.valueOf(data.get("is_warning")))?"信号告警":"上下限告警",
                                                    obj.startsWith("In")?"进水口":"出水口"
                                            );
                                        }
                                        String dataVal =  String.valueOf(data.get("item_val"));
                                        if(StrUtil.isNotEmpty(dataVal)&& !"null".equals(dataVal)){
                                            redisFlag = prefix+"_"+obj+"_curve";
                                            JSONArray jsonArray = null;
                                            if(myRedisTemplate.hasKey(redisFlag)){
                                                String curveData =  String.valueOf(myRedisTemplate.opsForValue().get(redisFlag));
                                                if(JSONUtil.isJsonArray(curveData)){
                                                    jsonArray = JSONUtil.parseArray(curveData);
                                                } else {
                                                    jsonArray = new JSONArray();
                                                }
                                            } else {
                                                jsonArray = new JSONArray();
                                            }
                                            JSONObject jsonObject = new JSONObject();
                                            jsonObject.put("item_name",data.get("item_name"));
                                            jsonObject.put("item_code",data.get("item_code"));
                                            jsonObject.put("item_val",data.get("item_val")==null?"":NumberUtils.formatNumber(String.valueOf(data.get("item_val"))));
                                            jsonObject.put("data_time",data.get("lastchange"));
                                            jsonArray.put(jsonObject);
                                            myRedisTemplate.opsForValue().set(redisFlag,JSONUtil.toJsonStr(jsonArray));
                                            myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                            redisFlag = prefix+"_"+obj+"_min";
                                            Double currentDataValue = Double.parseDouble(dataVal);
                                            DecimalFormat df = new DecimalFormat("######0.00");
                                            String dataValStr =  df.format(currentDataValue);
                                            if(myRedisTemplate.hasKey(redisFlag)){
                                                String minData =  String.valueOf(myRedisTemplate.opsForValue().get(redisFlag));
                                                Double minDataValue = Double.parseDouble(minData);
                                                if(currentDataValue < minDataValue){
                                                    myRedisTemplate.opsForValue().set(redisFlag,dataValStr);
                                                    myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                    redisFlag = prefix+"_"+obj+"_min_time";
                                                    myRedisTemplate.opsForValue().set(redisFlag, dateTime.toString("yyyy-MM-dd")+"_"+data.get("lastchange"));
                                                    myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                }

                                            } else {
                                                myRedisTemplate.opsForValue().set(redisFlag,dataValStr);
                                                myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                redisFlag = prefix+"_"+obj+"_min_time";
                                                myRedisTemplate.opsForValue().set(redisFlag, dateTime.toString("yyyy-MM-dd")+"_"+data.get("lastchange"));
                                                myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                            }
                                            redisFlag = prefix+"_"+obj+"_max";
                                            if(myRedisTemplate.hasKey(redisFlag)){
                                                String maxData =  String.valueOf(myRedisTemplate.opsForValue().get(redisFlag));
                                                Double maxDataValue = Double.parseDouble(maxData);
                                                if(currentDataValue > maxDataValue){
                                                    myRedisTemplate.opsForValue().set(redisFlag,dataValStr);
                                                    myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                    redisFlag = prefix+"_"+obj+"_max_time";
                                                    myRedisTemplate.opsForValue().set(redisFlag, dateTime.toString("yyyy-MM-dd")+"_"+data.get("lastchange"));
                                                    myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                }

                                            } else {
                                                myRedisTemplate.opsForValue().set(redisFlag,dataValStr);
                                                myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                                redisFlag = prefix+"_"+obj+"_max_time";
                                                myRedisTemplate.opsForValue().set(redisFlag, dateTime.toString("yyyy-MM-dd")+"_"+data.get("lastchange"));
                                                myRedisTemplate.expire(redisFlag,1,TimeUnit.DAYS);
                                            }
                                        }
                                    }
                                    logger.info("--------------------------------结束缓存第二场景数据,泵站{}，测量量{}的数据--------------------------",pompCode,obj);
                                }
                            }
                        });
                    }
                }
            });
        } catch (Exception e) {
            e.printStackTrace();
            logger.info(e.getMessage());
        }
        logger.info("--------------------------------结束缓存第二场景数据--------------------------");

    }

}
